The present invention relates to a database system, and particularly relates to a database system processing technique for partitioning a database table based on key range partitioning in a relational database system.
One of database systems is a relational database system, in which a database is constituted by a table that can be observed in a two-dimensional table format by a user, and the table is constituted by a plurality of rows. In addition, each row is constituted by a plurality of columns, and each column has a data type and a data length defining the properties of the column. In the relational database system, the table constituting the database is partitioned and stored to improve the throughput in the system. That is, in the database system, table data are fragmented and stored in a plurality of storage areas (horizontal (row) partitioning of the table) so that the load of accesses to the table can be dispersed. Key range partitioning is often used as the method for partitioning the table. According to key range partitioning, as described above, a range of data to be stored in each storage area as to one column in the table is specified as a condition, and given data is stored in a storage area satisfying the condition and selected based on the value of the data (for example, such a key range partitioning system is disclosed in “TRANSACTION PROCESSING: CONCEPTS AND TECHNIQUES, ISBN 1-55860-190-2, pp.820–821”).
Assume that data to be stored in time series, for example, sale history data is fragmented by date and stored. In such a case, in order to effectively use computer resources, unnecessary old data is deleted, and the area where the deleted data had been stored is used for storing new data. In this event, maintenance of partitions and storage areas due to the revision of the ranges of the data requires a management cost because the unnecessary data has to be deleted manually.
There is proposed another table partitioning system in which when a table constituting a relational database is judged necessary to suffer a change of table partitions, changing the table partitions is automated by recursive use of the table to be stored, so as to lighten the burden imposed on a user (for example, JP-A-2000-67077).
Data stored in time series is accessed at frequent intervals at first. However, the access frequency will decrease as time progresses, and finally access will be rarely gained to the data. Thus, according to simple key range partitioning, frequent access to a partition storing new data results in concentration of loads on the partition.
Therefore, in order avoid such a local concentration of loads on a database area, there has been also considered a management method in which data frequently accessed is fragmented into partitions each having a small range, while data infrequently accessed is managed with a large range.
However, in such a management method, when it is necessary to change the table partitions, it is necessary to rearrange the ranges and further migrate stored data in accordance with the rearranged ranges. Thus, a large number of commands are required. That is, in maintenance for partitions and storage areas, it is necessary to execute the following operations in turn; 1) to delete data stored in each unnecessary partition; 2) to delete definitions of the unnecessary partition; 3) to integrate ranges so that data fragmented in partitions each having a small range are rearranged in a partition having a large range; 4) to add a range to store new data; and so on.
In a technique disclosed in JP-A-2000-67077, only the operation to delete partitions and the operation to add partitions are executed automatically, but it is not possible to support the automation of the operation to integrate ranges so that data fragmented in partitions each having a small range are rearranged in a partition having a large range. Thus, a large burden is imposed on a user.